package com.mmy.dao.plane;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.plane.VPlaneOrder;
import com.mmy.domain.scenic.VScenicOrder;

public interface VPlaneOrderRepository extends JpaRepository<VPlaneOrder, Integer> {

	/*
	 * 根据ScenicID查找
	 */
	@Query(value = "select *  from vPlaneOrder where left (cOrderID,6) = :iPlaneID", nativeQuery = true)
	List<VPlaneOrder> findByPlaneID(@Param("iPlaneID") int iPlaneID);

	/*
	 * 根据出纳审核状态查找
	 */
	@Query(value = "select *  from vPlaneOrder where iCashierState = :strCashierState and left (cOrderID,6) = :iPlaneID", nativeQuery = true)
	List<VPlaneOrder> findByCashierAndPlaneID(@Param("strCashierState") String strCashierState,
			@Param("iPlaneID") int iPlaneID);

	/*
	 * 根据会计审核状态查找
	 */
	@Query(value = "select *  from vPlaneOrder where iAccountState = :strAccountState and left (cOrderID,6) = :iPlaneID", nativeQuery = true)
	List<VPlaneOrder> findByAccountAndPlaneID(@Param("strAccountState") String strAccountState,
			@Param("iPlaneID") int iPlaneID);

	/**
	 * 函数功能说明：后台管理员显示所有订单正式订单以及退单
	 *
	 * @param 参数说明
	 * @return 返回值说明 jsp页面
	 * @author 创建人 shangzl 修改人hanxl
	 * @date 创建日期 2021.01.12
	 */
	@Query(value = "select * from vPlaneOrder where iOrderState in(1,2,4)  order by dOrderDate  ", nativeQuery = true)

	List<VPlaneOrder> findBySeachAdministrator();

	@Query(value = "select * from vPlaneOrder where  iOrderState in (1,2,4) "
			+ "and (cSpare1=:iOrgID or (iOrderUserOrgID=:iOrgID and cSpare1<>:iOrgID)) "
			+ "order by dOrderDate desc  ", nativeQuery = true)
	List<VPlaneOrder> findBySearchOperator(@Param("iOrgID") int iOrgID);
	/**
	 * 后台超级管理员 查看景区订单时用到
	 *
	 * @author 创建人 hanxl
	 * @param date
	 * @date 创建日期 2021.01.25
	 */
	@Query(value = "select * from vPlaneOrder where iOrderState in(:lstOrderState) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strPlaneName = \"\" or cPlaneName like %:strPlaneName%) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc limit :startIndex,:count ", nativeQuery = true)
	List<VPlaneOrder> findBySeachAdmin(@Param("strPlaneName") String strPlaneName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("strOrderDate") String strOrderDate, @Param("lstOrderState") List<Integer> lstOrderState,
			@Param("startIndex") int startIndex, @Param("count") int count);

	@Query(value = "select count(*)  from vPlaneOrder where iOrderState in(:lstOrderState) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strPlaneName = \"\" or cPlaneName like %:strPlaneName%) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc  ", nativeQuery = true)
	int countALL(@Param("strPlaneName") String strPlaneName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("strOrderDate") String strOrderDate, @Param("lstOrderState") List<Integer> lstOrderState);

	@Query(value = "select * from vPlaneOrder where iOrderState in(:lstOrderState) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strPlaneName = \"\" or cPlaneName like %:strPlaneName%) "
			+ "and (cSpare1=:iOrgID or (iOrderUserOrgID=:iOrgID and cSpare1<>:iOrgID)) "
			
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc limit :startIndex,:count ", nativeQuery = true)
	List<VPlaneOrder> findBySeachOperator(@Param("strPlaneName") String strPlaneName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("strOrderDate") String strOrderDate, @Param("lstOrderState") List<Integer> lstOrderState, @Param("iOrgID") int iOrgID,	@Param("startIndex") int startIndex, @Param("count") int count);

	@Query(value = "select * from vPlaneOrder where iOrderState in(:lstOrderState) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strPlaneName = \"\" or cPlaneName like %:strPlaneName%) "
			+ "and (cSpare1=:iOrgID or (iOrderUserOrgID=:iOrgID and cSpare1<>:iOrgID)) "
			
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc  ", nativeQuery = true)
	int queryOrderCount(@Param("strPlaneName") String strPlaneName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("strOrderDate") String strOrderDate, @Param("lstOrderState") List<Integer> lstOrderState, @Param("iOrgID") int iOrgID);

}
